package com.atech.ebayadmin.merchandise.dao.impl;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;

import com.atech.ebayadmin.common.pager.Pager;
import com.atech.ebayadmin.common.util.BaseDAOHibernate;
import com.atech.ebayadmin.merchandise.dao.IMerchandiseDAO;
import com.atech.ebayadmin.merchandise.model.ModifyHistory;
import com.atech.ebayadmin.merchandise.model.Record;

public class MerchandiseDAO extends BaseDAOHibernate implements IMerchandiseDAO{
	private static org.apache.commons.logging.Log log = org.apache.commons.logging.LogFactory.getLog(MerchandiseDAO.class);
	@Override
	protected Class getObjClass() {
		return Record.class;
	}
	
	// Add for create record
	public Integer saveRecord(Record po) throws Exception{
		try{
			validateUniqueFields(po);
			System.err.println("[MerchandiseDAO].[saveRecord].po = " + po);
			Object key = super.getHibernateTemplate().save(po);
			return (Integer)key;
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}
	// Add for create record end
	
	// Add for list record
        // Revised by KK for Search Panel Enhancement on Aug30
	public List<Record> listRecord(Map vo, Pager pager) throws Exception{
		try{
			StringBuilder queryCountHql = new StringBuilder(this.queryCountRecord(vo));	
			StringBuilder queryListHql = new StringBuilder(this.queryListRecord(vo));
			Query queryCount = super.getSession().createQuery(queryCountHql.toString());
			Query queryList = super.getSession().createQuery(queryListHql.toString());
			int i =0;
			
			if(null != vo){
				Iterator it = vo.entrySet().iterator(); 
				while (it.hasNext()) { 
					Map.Entry entry = (Map.Entry) it.next(); 
					String key = (String) entry.getKey(); 
					String value = (String) entry.getValue();
					if (StringUtils.isNotBlank(value)) {
						if (StringUtils.contains("manDate,inputDate,modifyDate,useDate", key)) {
							queryCount.setString(key, value.trim() + "%");
							queryList.setString(key, value.trim()+ "%");
						} else {
							queryCount.setString(i, value.trim());
							queryList.setString(i, value.trim());
							i++;
						}
						System.err.println("=========SetSqlValue======" + key + "=" + value);
					}
				}
			}
			/*if (StringUtils.isNotBlank(vo.getProdType())) {
				queryCount.setString(i, vo.getProdType().trim());
				queryList.setString(i, vo.getProdType().trim());
				i++;
			}
			if (StringUtils.isNotBlank(vo.getProdClass())) {
				queryCount.setString(i, vo.getProdClass().trim());
				queryList.setString(i, vo.getProdClass().trim());
				i++;
			}
			if (StringUtils.isNotBlank(vo.getEquipClass())) {
				queryCount.setString(i, vo.getEquipClass().trim());
				queryList.setString(i, vo.getEquipClass().trim());
				i++;
			}
			if (StringUtils.isNotBlank(vo.getFiberType())) {
				queryCount.setString(i, vo.getFiberType().trim());
				queryList.setString(i, vo.getFiberType().trim());
				i++;
			}
			if (StringUtils.isNotBlank(vo.getCurrStatus())) {
				queryCount.setString(i, vo.getCurrStatus().trim());
				queryList.setString(i, vo.getCurrStatus().trim());
				i++;
			}
			/*if(StringUtils.isNotBlank(vo.getTitle())){
				queryCount.setString(i, "%"+vo.getTitle().trim()+"%");
				queryList.setString(i, "%"+vo.getTitle().trim()+"%");
				i++;
			}
			if(vo.getId() != null && vo.getId() !=0){
				queryCount.setInteger(i, vo.getId());
				queryList.setInteger(i, vo.getId());
				i++;
			}
			if(StringUtils.isNotBlank(vo.getEbayAccount())){
				queryCount.setString(i,vo.getEbayAccount().trim());
				queryList.setString(i, vo.getEbayAccount().trim());
				i++;
			}*/
			Number total = (Number)queryCount.uniqueResult();
			System.err.println(" ==== total number ====" + total);
			
			if(null != pager){
				System.err.println(" ==== total number ====" + pager.getStartRow());
				System.err.println(" ====getPageSizer ====" + pager.getPageSize());
				pager.setTotalRows(total.intValue());
				queryList.setFirstResult(pager.getStartRow());
				queryList.setMaxResults(pager.getPageSize());
			}
			List result = queryList.list();
			System.err.println("[MerchandiseDAO].[listRecord].result = " + result);
			return result;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	protected Class getRecordClass() {
		return Record.class;
	}
	private String queryCountRecord(Map vo){
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ");
		sb.append(getRecordClass().getName().toString());
		sb.append(this.createSqlString(vo));
		return sb.toString();
	}
	private String queryListRecord(Map vo){
		StringBuilder sb = new StringBuilder();
		sb.append("from ");
		sb.append(getRecordClass().getName().toString());
		sb.append(this.createSqlString(vo));
		return sb.toString();
	}
        //Revised by KK for Search Panel Enhancement on Aug30
	private String createSqlString(Map vo){		
		StringBuilder sb = new StringBuilder();
		StringBuilder fsb = new StringBuilder();
		sb.append(" t where 1=1 ");
		//Map map = new HashMap(); 
		if (null != vo) {
			Iterator it = vo.entrySet().iterator(); 
			while (it.hasNext()) { 
				Map.Entry entry = (Map.Entry) it.next(); 
				String key = (String) entry.getKey(); 
				String value = (String) entry.getValue(); 
				System.err.println("=========CreateSqlString======" + key + "=" + value);
				if (StringUtils.isNotBlank(value)) {
					if (StringUtils.contains("manDate,inputDate,modifyDate,useDate", key)) {
						fsb.append(" and ").append(key).append(" like:" + key);
					} else {
						sb.append(" and t.").append(key).append(" = ?");
					}	
				}
			}
			sb.append(fsb);
			sb.append(" order by t.sequenceNum desc ");
		}
		/*if (StringUtils.isNotBlank(vo.getProdType())) {
			sb.append(" and t.prodType = ?");
		}
		if (StringUtils.isNotBlank(vo.getProdClass())) {
			sb.append(" and t.prodClass = ?");
		}
		if (StringUtils.isNotBlank(vo.getEquipClass())) {
			sb.append(" and t.equipClass = ?");
		}
		if (StringUtils.isNotBlank(vo.getFiberType())) {
			sb.append(" and t.fiberType = ?");
		}
		if (StringUtils.isNotBlank(vo.getCurrStatus())) {
			sb.append(" and t.currStatus = ?");
		}
		/*if(StringUtils.isNotBlank(vo.getTitle())){
			sb.append(" and t.title like ?").append("");
		}
		if(vo.getId() != null && vo.getId() !=0){
			sb.append(" and t.id = ?");
		}
		if(StringUtils.isNotBlank(vo.getEbayAccount())){
			sb.append(" and t.ebayAccount = ?");
		}*/
		//sb.append(" order by t.assetsNum desc");
		return sb.toString();
	}
	// Add for list record end
	
	// Add for save modify history begin
		public Integer saveModHisty(ModifyHistory po) throws Exception{
			try{
				System.err.println("[MerchandiseDAO].[saveModHisty].po = " + po);
				Object key = super.getHibernateTemplate().save(po);
				return (Integer)key;
			}catch (Exception e) {
				e.printStackTrace();
				throw e;
			}
		}
		// Add for save modify history end
	
	// Add for retrieve modify history begin
	public List<ModifyHistory> rtrvModifyHisty(Integer key) throws Exception{
		try{	
			StringBuilder sb = new StringBuilder();
			sb.append("from ");
			sb.append(ModifyHistory.class.getName().toString());
			sb.append(" t where 1=1 ");
			sb.append("and t.").append("sequenceNum").append(" = ?");
			sb.append(" order by t.no desc");
			Query queryList = super.getSession().createQuery(sb.toString()).setMaxResults(10);
			System.err.println("histoy====" + key);
			queryList.setInteger(0, key);
			List result = queryList.list();
			System.err.println("[MerchandiseDAO].[rtrvModifyHisty].result = " + result);
			return result;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	// Add for retrieve modify history end
	
	// Add for delete modify history begin
	public int delSlctReModHisty(Integer key) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("delete from ");
		sb.append(ModifyHistory.class.getName().toString());
		sb.append(" t where 1=1 ");
		sb.append("and t.").append("sequenceNum").append(" = ?");
		Query query = super.getSession().createQuery(sb.toString());
		query.setInteger(0, key);
		int result = query.executeUpdate();
		return result;
	}
	// Add for delete modify history end
	
	// Add for modify record begin
	public Record getRecord(Integer id) throws Exception{
		System.err.println("[getRecord].id = " + id.intValue());
		Record po = (Record)super.getHibernateTemplate().get(getRecordClass(), id);
		return po;
	}
	public void updateRecord(Record po) throws Exception {
		System.err.println("[MerchandiseDAO].[updateRecord].po = " + po);
		//Commented by Doug on Oct22: assetsNum & SN should not be revised in modify_record.jsp so no need to check those now
		//validateDuplicateKey(po);
		//Commented by Doug on Dec10: assetsNum & SN should not be revised in modify_record.jsp so no need to check those now
		/*validateUniqueFields(po);*/
		super.updateObject(po);
	}
	// Add for modify record end
	
	// Add for delete select record begin
	public void deleteSlctRe(Record po) throws Exception{
		super.deleteObject(po);
	}
	// Add for delete select record end
	
	public void validateUniqueFields(Record po) throws Exception{
		//sequenceNum, assetsNum, sn
		String errorMsg = "";

		System.err.println("[MerchandiseDAO].[validateUniqueFields].po = " + po);
		
		String assetsNum = po.getAssetsNum();
		Integer sequenceNum = po.getSequenceNum();
		if(null != assetsNum){
			StringBuilder sb = new StringBuilder();
			sb.append("select count(*) from ");
			sb.append(getRecordClass().getName().toString());
			sb.append(" t where 1=1 ");
			sb.append("and t.").append("assetsNum").append(" = ?");
			if(null != sequenceNum){
				sb.append("and t.").append("sequenceNum").append(" != ?");
			}

			System.err.println("SQL = " + sb.toString());
			Query queryCount = null;
			Session sqlSession = null;
			Number total = null;
			try {
				sqlSession = this.getHibernateTemplate().getSessionFactory().openSession();
				 
				queryCount = sqlSession.createQuery(sb.toString());
				
				queryCount.setString(0, assetsNum.trim());
				
				if(null != sequenceNum){
					queryCount.setInteger(1, po.getSequenceNum());
				}
				
				total = (Number)queryCount.uniqueResult();
				
			} catch (Exception e) {
				throw e;
			} finally {
				sqlSession.close();
			}

			System.err.println("total = " + total.intValue());
			
			if(total.intValue() >0 ){
				errorMsg = errorMsg + " 资产号有重复，重复值为："+assetsNum;
			}
		}

		String sn = po.getSn();
		/*if(null != sn){*/
		//Revised by Doug on Oct24.
		//As requested by customer, blank SN is allowed & no need to check duplicate case 
		if(StringUtils.isNotBlank(sn)){
			StringBuilder sb = new StringBuilder();
			sb.append("select count(*) from ");
			sb.append(getRecordClass().getName().toString());
			sb.append(" t where 1=1 ");
			sb.append("and t.").append("sn").append(" = ?");
			
			if(null != sequenceNum){
				sb.append("and t.").append("sequenceNum").append(" != ?");
			}

			System.err.println("SQL = " + sb.toString());
			Query queryCount = null;
			Session sqlSession = null;
			Number total = null;
			try {
				sqlSession = this.getHibernateTemplate().getSessionFactory().openSession();
				
				queryCount = sqlSession.createQuery(sb.toString());
				queryCount.setString(0, sn.trim());
				
				if(null != sequenceNum){
					queryCount.setInteger(1, po.getSequenceNum());
				}
				
				total = (Number)queryCount.uniqueResult();
			} catch (Exception e) {
				throw e;
			} finally {
				sqlSession.close();
			}
			
			System.err.println("total = " + total.intValue());
			if(total.intValue() >0 ){
				errorMsg = errorMsg + " S/N有重复，重复值为："+sn;
			}
		}
		
		if(StringUtils.isNotBlank(errorMsg)){
			throw new Exception(errorMsg);
		}
		
	}
	
	public void validateDuplicateKey(Record po) throws Exception{
		String errorMsg = "";

		System.err.println("[MerchandiseDAO].[validateDuplicateKey].po = " + po);
		if(null != po.getSequenceNum()){
			StringBuilder sb = new StringBuilder();
			sb.append("select count(*) from ");
			sb.append(getRecordClass().getName().toString());
			sb.append(" t where 1=1 ");
			sb.append("and t.").append("sequenceNum").append(" = ?");
			System.err.println("SQL = " + sb.toString());
			Query queryCount = null;
			Session sqlSession = null;
			Number total = null;
			try {
				sqlSession = this.getHibernateTemplate().getSessionFactory().openSession();
				queryCount = sqlSession.createQuery(sb.toString());
				queryCount.setInteger(0, po.getSequenceNum());
				total = (Number)queryCount.uniqueResult();
			} catch (Exception e) {
				throw e;
			} finally {
				sqlSession.close();
			}

			System.err.println("total = " + total.intValue());
			if(total.intValue() >0 ){
				errorMsg = " 序号有重复，重复值为："+po.getSequenceNum().intValue();
			}
		}

		if(StringUtils.isNotBlank(errorMsg)){
			throw new Exception(errorMsg);
		}
	}
	
	//Added by Doug on Dec-11. In jsp level, Date should have a valid format
	public void validateDateField(Record po) throws Exception{
		String errorMsg = "";

		System.err.println("[MerchandiseDAO].[validateDateField].po = " + po);
		
		String assetsNum = po.getAssetsNum();
		Integer sequenceNum = po.getSequenceNum();
		if(null != assetsNum){
			StringBuilder sb = new StringBuilder();
			sb.append("select count(*) from ");
			sb.append(getRecordClass().getName().toString());
			sb.append(" t where 1=1 ");
			sb.append("and t.").append("assetsNum").append(" = ?");
			if(null != sequenceNum){
				sb.append("and t.").append("sequenceNum").append(" != ?");
			}

			System.err.println("SQL = " + sb.toString());
			Query queryCount = null;
			Session sqlSession = null;
			Number total = null;
			try {
				sqlSession = this.getHibernateTemplate().getSessionFactory().openSession();
				 
				queryCount = sqlSession.createQuery(sb.toString());
				
				queryCount.setString(0, assetsNum.trim());
				
				if(null != sequenceNum){
					queryCount.setInteger(1, po.getSequenceNum());
				}
				
				total = (Number)queryCount.uniqueResult();
				
			} catch (Exception e) {
				throw e;
			} finally {
				sqlSession.close();
			}

			System.err.println("total = " + total.intValue());
			
			if(total.intValue() >0 ){
				errorMsg = errorMsg + " 资产号有重复，重复值为："+assetsNum;
			}
		}

		if(StringUtils.isNotBlank(errorMsg)){
			throw new Exception(errorMsg);
		}
		
	}

}
